package com.example.demo.repository;

import com.example.demo.entity.Pick.PickDTO;
import com.example.demo.entity.Pick.PickProduceWpTakeGoods;
import jakarta.transaction.Transactional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Repository
public interface PickRepository extends JpaRepository<PickProduceWpTakeGoods, String> {

    List<PickProduceWpTakeGoods> getAllByProductBillNo(String productBillNo);

    @Query(value = "SELECT WA_produce_bill_detail_m.wp_code," +
            "b_working_procedure.wp_name," +
            "WA_produce_wp_take_return_detail.product_bill_no," +
            "V_goods.standard_code," +
            "WA_produce_wp_take_return_detail.goods_code," +
            "V_goods.goods_name," +
            "V_goods.design_number_ver_spec," +
            "WA_produce_bill_detail_m.require_qty - WA_produce_bill_detail_m.already_take_qty + WA_produce_bill_detail_m.already_return_qty as can_take_qty," +
            "WA_produce_bill_detail_m.require_qty - WA_produce_bill_detail_m.already_apply_stock_qty + WA_produce_bill_detail_m.already_return_qty as can_apply_stock_qty," +
            "WA_produce_wp_take_return_detail.qty," +
            "p_wa_location_stock.working_area_code," +
            "b_wa_location.working_center_code," +
            "b_wa_location.wa_location_name," +
            "b_working_area.working_area_code," +
            "p_wa_location_stock.on_hand_qty " +
            "FROM " +
            "    WA_produce_wp_take_return_detail " +
            "    LEFT OUTER JOIN V_goods ON WA_produce_wp_take_return_detail.goods_code = V_goods.goods_code " +
            "    LEFT OUTER JOIN WA_produce_bill_detail_m ON WA_produce_wp_take_return_detail.product_bill_no = WA_produce_bill_detail_m.bill_no " +
            "       AND WA_produce_wp_take_return_detail.goods_code = WA_produce_bill_detail_m.goods_code " +
            "       AND WA_produce_wp_take_return_detail.wp_id = WA_produce_bill_detail_m.wp_id" +
            "    LEFT OUTER JOIN WA_produce_bill_master ON WA_produce_bill_detail_m.bill_no = WA_produce_bill_master.bill_no " +
            "    LEFT OUTER JOIN V_goods V_goods_b ON WA_produce_bill_master.goods_code = V_goods_b.goods_code " +
            "    LEFT OUTER JOIN V_goods V_goods_c ON WA_produce_bill_master.product_code = V_goods_c.goods_code " +
            "    LEFT OUTER JOIN b_working_procedure ON WA_produce_bill_detail_m.wp_code = b_working_procedure.wp_code " +
            "    LEFT OUTER JOIN p_wa_location_stock ON WA_produce_wp_take_return_detail.goods_code = p_wa_location_stock.goods_code " +
            "    LEFT OUTER JOIN b_wa_location ON p_wa_location_stock.location_code = b_wa_location.wa_location_code " +
            "    LEFT OUTER JOIN b_working_area ON p_wa_location_stock.working_area_code = b_working_area.working_area_code " +
            "where WA_produce_wp_take_return_detail.product_bill_no = :productBillNo " +
            "and WA_produce_wp_take_return_detail.wp_id = :wpID " +
            "and WA_produce_wp_take_return_detail.goods_code = :goodsCode", nativeQuery = true)
    List<PickDTO> getPickDetail(String productBillNo, int wpID, String goodsCode);

    @Modifying
    @Transactional
    @Query(value = "insert into WA_produce_wp_take_return_master(bill_no, bill_status, bill_type, in_out_date, working_area_code, take_return_working_area_code, create_by, create_date, stockpreparation_status, take_return_person, worker_code) " +
            "values(:billNo, :billStatus, :billType, :inOutDate, :workingAreaCode, :takeReturnWorkingAreaCode, :createBy, :createDate, :stockPreparationStatus, :takeReturnPerson, :workerCode)", nativeQuery = true)
    void saveMaster(String billNo, String billStatus, String billType, String inOutDate, String workingAreaCode, String takeReturnWorkingAreaCode, String createBy, String createDate, String stockPreparationStatus, String takeReturnPerson, String workerCode);

    @Modifying
    @Transactional
    @Query(value = "insert into WA_produce_wp_take_return_detail(bill_no, product_bill_no, wp_id, goods_code, qty) " +
            "values(:billNo, :productBillNo, :wpID, :goodsCode, :qty)", nativeQuery = true)
    void saveDetail(String billNo, String productBillNo, int wpID, String goodsCode, float qty);

    @Query(value = "select wp_id from WA_produce_bill_detail_m where bill_no = :productBillNo and goods_code = :goodsCode and wp_code = :wpCode", nativeQuery = true)
    int getWpIDByWpCode(String productBillNo, String goodsCode, String wpCode);

    @Query(value = "select worker_name from b_worker where worker_code = :code and worker_status = '在职'", nativeQuery = true)
    String getWorker(String code);

    @Query(value = "select case when exists( " +
            "select 1 from WA_produce_wp_take_return_detail " +
            "where bill_no = :billNo and " +
            "product_bill_no = :productBillNo and " +
            "wp_id = :wpID and " +
            "goods_code = :goodsCode" +
            ") then 1 else 0 end", nativeQuery = true)
    int checkDetail(String billNo, String productBillNo, String goodsCode, int wpID);

    @Modifying
    @Query(value = "update WA_produce_wp_take_return_detail set qty = :qty where bill_no = :billNo and product_bill_no = :productBillNo and wp_id = :wpID and goods_code = :goodsCode", nativeQuery = true)
    void updateDetail(String billNo, String productBillNo, String goodsCode, int wpID, BigDecimal qty);

    @Query(value = "select case when exists( " +
            "select 1 from WA_produce_wp_take_return_master where bill_no = :billNo " +
            ") then 1 else 0 end", nativeQuery = true)
    int checkMaster(String billNo);

    @Modifying
    @Query(value = "update WA_produce_wp_take_return_master set in_out_date = :inOutDate, working_area_code = :workingAreaCode, take_return_working_area_code = :takeReturnWorkingAreaCode, take_return_person = :takeReturnPerson, worker_code = :workerCode where bill_no = :billNo", nativeQuery = true)
    void updateMaster(String billNo, String inOutDate, String workingAreaCode, String takeReturnWorkingAreaCode, String takeReturnPerson, String workerCode);
}
